﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NG
{
    class qAskedHandler:IQaskedHandler 
    {
        private string tableName = "[dbo].[QAsked]";

        public void AskQuestion(QAsked que)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("INSERT INTO" + tbn + "(interview,originalQ,qDesc,ksf,qOrder,isObservable)VALUES("
                + "{0},{1},'{2}',{3},{4},'{5}');" + "SELECT qAskedNo FROM" + tbn + "WHERE qAskedNo = @@IDENTITY;", que.interview, que.originalQ, 
                que.questionDesc, que.ksf, que.qOrder, que.isObservable);
            
            SqlCommand cmd = new SqlCommand(query, con.connection);
            que.qAskedNo = int.Parse(cmd.ExecuteScalar().ToString());
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void deleteAskedQuestion(QAsked que)
        {
           
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;

            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }

            string query = "DELETE FROM " + tbn + " WHERE qAskedNo = " + que.qAskedNo;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public void updateAskedQue(QAsked que , QAsked nque)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = string.Format("UPDATE" + tbn + " SET interview = {0} ,originalQ = {1},qDesc= '{2}',ksf= {3},qOrder={4},isObservable= '{5}'"
                + "WHERE qAskedNo = {6}", nque.interview, nque.originalQ, nque.questionDesc, nque.ksf, nque.qOrder, nque.isObservable,nque.qAskedNo);
            SqlCommand cmd = new SqlCommand(query, con.connection);
            cmd.ExecuteNonQuery();
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public LinkedList<QAsked> allAskedQuestions(string sortedBy,bool desc)
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = "{0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<QAsked> qAskedList = new LinkedList<QAsked>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT * FROM " + tbn + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                   
                    QAsked inter = new QAsked(int.Parse(rdr["interview"].ToString()),
                            int.Parse(rdr["originalQ"].ToString()), rdr["qDesc"].ToString(),
                                   int.Parse(rdr["ksf"].ToString()), int.Parse(rdr["qOrder"].ToString()), bool.Parse(rdr["isObservable"].ToString()));


                    qAskedList.AddLast(inter);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return qAskedList;
        }
    }
}
